lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-09-22(胡工).sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_ErpWageCommissionRecords](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Wcr_EmployeeID] [nvarchar](20) NULL,
	[Wcr_CommissionScheme] [nvarchar](25) NULL,
	[Wcr_CurrentPerformance] [decimal](18, 2) NULL,
	[Wcr_PieceCommission] [decimal](18, 2) NULL,
	[Wcr_CommissionWages] [decimal](18, 2) NULL,
	[Wcr_PerformanceTimeStart] [datetime] NULL,
	[Wcr_PerformanceTimeEnd] [datetime] NULL,
	[Wcr_CreateDateTime] [datetime] NULL,
	[Wcr_CreateName] [nvarchar](20) NULL,
 CONSTRAINT [PK_tb_ErpWageCommissionRecords] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'Wcr_EmployeeID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'提成方案编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'Wcr_CommissionScheme'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前业绩' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'Wcr_CurrentPerformance'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'计件提成' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'Wcr_PieceCommission'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'提成工资' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'Wcr_CommissionWages'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业绩开始时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'Wcr_PerformanceTimeStart'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业绩结束时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'Wcr_PerformanceTimeEnd'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'录入时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'Wcr_CreateDateTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'录入人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords', @level2type=N'COLUMN',@level2name=N'Wcr__CreateName'
GO
EXEC sys.sp_addextendedproperty @name=N'tb_ErpWageCommissionRecords', @value=N'工资提成记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpWageCommissionRecords'
GO



IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpWageCommissionRecords')
BEGIN
	DROP VIEW [dbo].Vw_ErpWageCommissionRecords
END 
GO
create View Vw_ErpWageCommissionRecords
as
SELECT ID
       ,Wcr_EmployeeID as 员工编号
      ,(select [User_Name] from tb_ErpUser where Wcr_EmployeeID=User_EmployeeID) as 员工姓名
	  ,Wcr_CommissionScheme as 提成方案编号
      ,(select Sc_ClassName from tb_ErpSystemCategory where Wcr_CommissionScheme=Sc_ClassCode)  as 提成方案名称
      ,Wcr_CurrentPerformance as 当前业绩
      ,Wcr_PieceCommission as 计件提成
      ,Wcr_CommissionWages as 提成工资
      ,Wcr_PerformanceTimeStart as 业绩开始时间
      ,Wcr_PerformanceTimeEnd as 业绩结束时间
      ,Wcr_CreateDateTime as 录入时间
      ,Wcr_CreateName as 录入人编号
	   ,(select [User_Name] from tb_ErpUser where Wcr_CreateName=User_EmployeeID) as 录入人名称
  FROM tb_ErpWageCommissionRecords
   GO



IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_Customer_PaymentOrders')
BEGIN
	DROP VIEW [dbo].Vw_Customer_PaymentOrders
END 
GO
create View Vw_Customer_PaymentOrders
as
select 
tb_ErpOrder.ID
,Ord_Number
,Ord_DividedShop
,Ord_Type 
,Ord_OrderClass
,Ord_PhotographyCategory
,Ord_SeriesName
,Ord_SeriesPrice
,Ord_Class
,GP_OrderNumber
,GP_CustomerGroupID
,Cus_Name
,Cus_Name_py
,Cus_Telephone
,M_Cus_CustomerNumber
,(select stuff((select ','+ OrdPe_OrdersPerson from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPersonID
,(select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPerson
,Ord_CreateDatetime
,(  SELECT count(id) FROM  tb_ErpOrderProductList where  OPlist_OrderNumber=Ord_Number and OPlist_PickupStatus=0) as PickupStatusCount 
,Ord_SinceOrderNumber
from tb_ErpOrder
left join tempTB_AggregationCustomer on Ord_Number=GP_OrderNumber
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWagePaymentRecords')
BEGIN
	DROP VIEW [dbo].Vw_OrdersWagePaymentRecords
END 
GO
create View Vw_OrdersWagePaymentRecords
as
SELECT   
tb_ErpPayment.ID, 
Pay_OrdNumber,
Pay_AmountOf,
Pay_OpenSingle, 
Pay_ThePayee,
Pay_PaymentMethod,
Pay_OrdersLocation,
Pay_ReceivableProject,
Pay_Remark, 
convert(varchar(10),Pay_CreateDatetime,120) as Pay_CreateDatetime,
Pay_Category,
Pay_TwoPinsCategory, 
dbo.fn_CheckUserIDGetUserName(Pay_OpenSingle) AS Pay_UserName, 
dbo.fn_CheckUserIDGetUserName(Pay_ThePayee) AS Pay_ThePayeeName, 
dbo.fn_GetClassCodeToName(Pay_PaymentMethod, Pay_PaymentMethod) AS Pay_PaymentMethodName, 
dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory) AS Pay_TwoPinsCategoryName, 
Pay_FinancialAuditdPeople,
Pay_FinancialAudit,
dbo.fn_CheckUserIDGetUserName(Pay_FinancialAuditdPeople)AS Pay_FinancialAuditdPeopleName, 
Pay_ShootingName,
Pay_Type,
Ord_DividedShop,
Ord_Type,
Cus_Name as Ord_CustomerName1,
(select Tsorder_Name from Vw_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_Name,
(select Tsorder_CustomerName from Vw_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_CustomerName,
(select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) as Cus_Name,
Ord_CreateDatetime,
Ord_SeriesName,
Ord_PhotographyCategory
,Ord_SinceOrderNumber
FROM    tb_ErpPayment
left join Vw_Customer_PaymentOrders on Pay_OrdNumber=Ord_Number

GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpPieceCommissionRecords')
BEGIN
	DROP VIEW [dbo].Vw_ErpPieceCommissionRecords
END 
GO
create View Vw_ErpPieceCommissionRecords
as
SELECT tb_ErpPieceCommissionRecords.ID
      ,Pcr_OrderNumber
      ,Pcr_DigitalDivision
      ,Pcr_Date
      ,Pcr_CompletionContents
      ,Pcr_Quantity
      ,Pcr_CreateTime
      ,Pcr_EntryPeople
      ,Pcr_Type
	  ,(select [User_Name] from tb_ErpUser where Pcr_DigitalDivision=User_EmployeeID) as Pcr_DigitalDivisionName
	  ,[dbo].[fn_GetClassCodeToName](Pcr_CompletionContents,Pcr_CompletionContents) as Pcr_CompletionContentsName
	  ,(select [User_Name] from tb_ErpUser where Pcr_EntryPeople=User_EmployeeID) as Pcr_EntryPeopleName
	  ,Ord_Type
	  ,Ord_Class
	  ,(select Cus_Name from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Name
	  ,(select Cus_Telephone from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Telephone
	  ,Ord_SeriesName
	  ,Ord_SeriesPrice
	  ,Pcr_Quantity*(select Wcs_Percentage from tb_ErpWageCommissionSet where Pcr_CompletionContents=Wcs_TypeCode) as 总价格
	  ,Ord_SinceOrderNumber
  FROM tb_ErpPieceCommissionRecords
  left join tb_ErpOrder on tb_ErpPieceCommissionRecords.Pcr_OrderNumber=tb_ErpOrder.Ord_Number
GO



IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_OrdersPerson')
BEGIN
	DROP VIEW [dbo].Vw_StaffPerformance_OrdersPerson
END 
GO
create View Vw_StaffPerformance_OrdersPerson
as
SELECT 
tb_ErpPayment.ID
,Pay_OrdNumber as 订单号
,Pay_ShootingName as 拍摄阶段
,Pay_Category as 收款类别
,Pay_TwoPinsCategory  as 二销类别编号
,dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory) AS 二销类别名称
,Pay_AmountOf as 收款金额
,Pay_OpenSingle as  接单人编号
, dbo.fn_CheckUserIDGetUserName(Pay_OpenSingle) AS 接单人名称
,Pay_ThePayee as 收款人编号
,dbo.fn_CheckUserIDGetUserName(Pay_ThePayee) AS 收款人名称
,Pay_PaymentMethod as 付款方式编号
,dbo.fn_GetClassCodeToName(Pay_PaymentMethod, Pay_PaymentMethod) AS 付款方式名称
,Pay_OrdersLocation as 接单地点 
,Pay_ReceivableProject as 收款项目
,Pay_FinancialAudit as 审核状态
,Pay_FinancialAuditdPeople as 审核人
,Pay_Remark as 备注
,Pay_CreateDatetime as 收款时间
,Pay_Type as 收款类型 
,(case Pay_Type
       when 0 then (select Cus_Name from tempTB_AggregationCustomer where Pay_OrdNumber=GP_OrderNumber)
	   when 1 then (select Tsorder_CustomerName from Vw_TwoSalesOrder where Pay_OrdNumber=Tsorder_Number)
	   when 2 then (select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number)  else '' end) as '客户名称'
,(case Pay_Type
		when 0 then (select Ord_PhotographyCategory from tb_ErpOrder where Pay_OrdNumber=Ord_Number)else '' end) as '套系类别'
,(case Pay_Type
		when 0 then (select Ord_SeriesName from tb_ErpOrder where Pay_OrdNumber=Ord_Number)else '' end) as '套系名称'
,(case Pay_Type
		when 0 then (select Ord_OrderClass from tb_ErpOrder where Pay_OrdNumber=Ord_Number)else '' end) as '订单类别'
,(case Pay_Type
		when 0 then (select Ord_SeriesPrice from tb_ErpOrder where Pay_OrdNumber=Ord_Number)
		when 1 then (select Tsorder_Money from Vw_TwoSalesOrder where Pay_OrdNumber=Tsorder_Number)
	    when 2 then (select Dsro_Amount from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) end) as '应收金额'
,(case Pay_Type
		when 0 then (dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory)) 
		when 1 then (dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory)) 
	    when 2 then Pay_ReceivableProject end) as '项目名称'
,case when (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') is null then '0' else (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') end as '主门市比重'
,case when (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') is null then '0' else (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') end as '副门市比重'
, (  case LEFT(Pay_OpenSingle,charindex(',',Pay_OpenSingle,1)) 
         when '' then Pay_OpenSingle
		 else  LEFT(Pay_OpenSingle,charindex(',',Pay_OpenSingle,1)-1)  end)as '主门市'
,len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))) as '副门市个数'
--, (  case Pay_Category
--         when '后期收款' then ( len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))
--		 else (select count(*) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Pay_OrdNumber  and OrdPe_Type='1') end) as '副门市个数'

,(case (len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))
		 when 0 then Pay_AmountOf
		 else  cast(Pay_AmountOf * case when (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') is null then '0' else (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') end*0.01  as   numeric(9,2)) 
 end) as '主门市金额'
,(case (len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))
         when 0 then 0
		 else  cast(Pay_AmountOf * case when (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') is null then '0' else (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') end*0.01/(len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))  as   numeric(9,2)) 
 end) as '副门市金额'
 ,(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) as 副订单号
,(case (select Ord_Type from tb_ErpOrder where Ord_Number=Pay_OrdNumber)
   when 0 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) 
   when 1 then (select Ordpg_PhotographyTime from tb_ErpOrdersPhotography where Ordpg_ViceNumber=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) ) 
   when 2 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) 
   else '' end) as 最后拍摄时间
 ,(case (select Ord_Type from tb_ErpOrder where Ord_Number=Pay_OrdNumber)
   when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 1 then (select count(id) as id from tb_ErpOrdersPhotography where  Ordpg_ViceNumber=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber)  and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   else '' end) as 未拍个数
,(case (select Ord_Type from tb_ErpOrder where Ord_Number=Pay_OrdNumber)
   when 0 then (select Ordv_FilmSelectionStatus from tb_ErpOrderDigital where Ordv_Number=Pay_OrdNumber)
   when 1 then (select  Ordv_FilmSelectionStatus from tb_ErpOrderDigital where  Ordv_ViceNumber=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber))
   when 2 then (select Ordv_FilmSelectionStatus from tb_ErpOrderDigital where Ordv_Number=Pay_OrdNumber)
   else '' end) as 选片状态
 ,(case (select Ord_Type from tb_ErpOrder where Ord_Number=Pay_OrdNumber)
   when 0 then (select Ordv_FilmSelectionTime from tb_ErpOrderDigital where Ordv_Number=Pay_OrdNumber)
   when 1 then (select  Ordv_FilmSelectionTime from tb_ErpOrderDigital where  Ordv_ViceNumber=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber))
   when 2 then (select Ordv_FilmSelectionTime from tb_ErpOrderDigital where Ordv_Number=Pay_OrdNumber)
   else '' end) as 选片时间
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = (select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期
,Ord_CustomerSource as 客户来源
,(select sum(Pay_AmountOf)  from tb_ErpPayment where  Ord_Number=Pay_OrdNumber and  Pay_Category!='后期收款') as 前期实收金额
,Ord_SinceOrderNumber as 自定义订单号
FROM tb_ErpPayment
left join tb_ErpOrder on Pay_OrdNumber=Ord_Number
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_Photograph')
BEGIN
	DROP VIEW [dbo].Vw_StaffPerformance_Photograph
END 
GO
create View Vw_StaffPerformance_Photograph
as
SELECT 
Ordv_Number as 主订单
,Ordv_ViceNumber as 副订单
,(select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) as 订单类型
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 拍摄名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number) 
   when 1 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber) 
   when 2 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number) 
   else '' end) as 最后拍摄时间
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 1 then (select count(id) as id from tb_ErpOrdersPhotography where  Ordv_ViceNumber=Ordpg_ViceNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   else '' end) as 未拍个数
,(case Ordv_FilmSelectionStatus when 1 then 'OK'  else '未选' end) as 选片状态
,Ordv_FilmSelectionTime as 选片时间
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADDEBFJDFFC')
   when 1 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_ViceNumber=Ordv_ViceNumber and Ordpg_SightsLevel='BEBBBCADDEBFJDFFC')
   when 2 then  (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADDEBFJDFFC')
   else '' end) as '景点一级个数'
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADEGBGAFFJC')
   when 1 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_ViceNumber=Ordv_ViceNumber and Ordpg_SightsLevel='BEBBBCADEGBGAFFJC')
   when 2 then  (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADEGBGAFFJC')
   else '' end) as '景点二级个数'
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADAFBHBCHCI')
   when 1 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_ViceNumber=Ordv_ViceNumber and Ordpg_SightsLevel='BEBBBCADAFBHBCHCI')
   when 2 then  (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADAFBHBCHCI')
   else '' end) as '景点三级个数'
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主摄影师ID
 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主摄影师名称
 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 摄影助理ID
 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 摄影助理名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主化妆ID
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主化妆名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 化妆助理ID
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 化妆助理名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 引导师ID
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 引导师名称
,Ordv_EarlyRepairName as '初修师ID'
,dbo.fn_CheckUserIDGetUserName(Ordv_EarlyRepairName) as '初修师'
,Ordv_RefinementName as '精修师ID'
,dbo.fn_CheckUserIDGetUserName(Ordv_RefinementName) as '精修师'
,Ordv_DesignerName as '设计师ID'
,dbo.fn_CheckUserIDGetUserName(Ordv_DesignerName) as '设计师'
,Vw_StaffPerformance_OrdersPerson.ID
,订单号
,拍摄阶段
,收款类别
,二销类别编号
,二销类别名称
,收款金额
,接单人编号
,接单人名称
,收款人编号
,收款人名称
,付款方式编号
,付款方式名称
,接单地点
,收款项目
,审核状态
,审核人
,备注
,收款时间
,收款类型
,客户名称
,套系类别
,套系名称
,订单类别
,应收金额
,项目名称
,客户来源
,自定义订单号
FROM tb_ErpOrderDigital 
left join Vw_StaffPerformance_OrdersPerson on Ordv_Number=订单号
where 订单号 is not null

GO